package com.mjgy.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mjgy.entity.PayLog;

/**
* @Description: TODO
* @author quanlq
* @date 2018年10月9日
* @version V1.0
*/
public interface PayLogRepository extends JpaRepository<PayLog, Long>, JpaSpecificationExecutor<PayLog> {

	PayLog findOneByOutTradeNo(String outTradeNo);
	
	List<PayLog> findByPayerId(Long userId);
	
	@Query(value = "SELECT COUNT(DISTINCT(tt.date)) FROM (SELECT LEFT(create_time, 10) date FROM pay_log WHERE status = 1 " + 
			"AND (LEFT(create_time, 10) >= :startDate OR :startDate IS NULL OR :startDate = '') " +
			"AND (LEFT(create_time, 10) <= :endDate OR :endDate IS NULL OR :endDate = '') " +
			"GROUP BY LEFT(create_time, 10), type " +
			"ORDER BY LEFT(create_time, 10) DESC, type) tt"
			,nativeQuery = true)
	Long countStatistic(@Param("startDate") String startDate, @Param("endDate") String endDate);
	
	@Query(value = "SELECT COUNT(DISTINCT(tt.city)) FROM " +
			"(SELECT usr.city FROM pay_log pl " +
			"JOIN e_user usr ON pl.payer_id = usr.id WHERE pl.status = 1 " + 
			"AND (LEFT(pl.create_time, 10) = :date OR :date IS NULL OR :date = '') " +
			"GROUP BY usr.city, pl.type " +
			"ORDER BY usr.city, pl.type) tt"
			,nativeQuery = true)
	Long countStatisticByCity(@Param("date") String date);
	
}
